﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PM.Entity;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class BankDepositServices
    {
        private static BankDeposit GetBankDepositFromDataReader(IDataReader dataReader)
        {
            BankDeposit retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new BankDeposit();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.BankId = dataReader["BANK_ID"].ToString();
                retVal.AccNo = dataReader["ACC_NO"].ToString();
                retVal.AccType = dataReader["ACC_TYPE"].ToString();
                decimal investValue = 0.0M;
                decimal.TryParse(dataReader["INVEST_VALUE"].ToString(), out investValue);
                retVal.InvestValue = investValue;

                DateTime investDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["INVEST_DATE"].ToString(), out investDate))
                {
                    retVal.InvestDate = investDate;
                }
                DateTime maturityDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["MATURITY_DATE"].ToString(), out maturityDate))
                {
                    retVal.MaturityDate = maturityDate;
                }

                decimal rate = 0.0M;
                decimal.TryParse(dataReader["RATE"].ToString(), out rate);
                retVal.Rate = rate;

                int active = 0;
                int.TryParse(dataReader["ACTIVE"].ToString(), out active);
                if (active == 1)
                {
                    retVal.Active = true;
                }
                else
                {
                    retVal.Active = false;
                }

                retVal.AccruedInterest = AccuredInterestServices.GetAccruedInterestByKey(retVal.AccNo);
            }
            return retVal;
        }

        public static BankDepositCollection GetBankDepositCollection()
        {
            BankDepositCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_DEPOSIT order by CLIENT_ID";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankDepositCollection();
                    while (dataReader.Read())
                    {
                        BankDeposit item = GetBankDepositFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankDepositCollection GetBankDepositCollectionByClientID(string clientId)
        {
            BankDepositCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_DEPOSIT where CLIENT_ID = :v1 order by CLIENT_ID";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankDepositCollection();
                    while (dataReader.Read())
                    {
                        BankDeposit item = GetBankDepositFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankDeposit GetBankDepositByIDs(string clientId, string bankId, string accNo)
        {
            BankDeposit retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_DEPOSIT where CLIENT_ID = '" + clientId + "' " +
                                                               "and BANK_ID = '" + bankId + "' " +
                                                               "and ACC_NO = '" + accNo + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = new BankDeposit();
                        retVal = GetBankDepositFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankDeposit GetBankDepositByAccNo(string accNo)
        {
            BankDeposit retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_DEPOSIT where ACC_NO = '" + accNo + "' and ACTIVE = 1 ";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = new BankDeposit();
                        retVal = GetBankDepositFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static List<string> GetAccNoCollection()
        {
            List<string> retVal = new List<string>();           
            
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select ACC_NO from BANK_DEPOSIT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        retVal.Add(dataReader["ACC_NO"].ToString());
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void ModifyDepositByTransaction(string accNo, string deb_cre, decimal amount)
        {
            BankDeposit retVal = GetBankDepositByAccNo(accNo);

            if (retVal != null)
            {
                retVal.AccNo = accNo;
                if (deb_cre == "D")
                {
                    retVal.InvestValue = retVal.InvestValue + amount;
                }
                else
                {
                    retVal.InvestValue = retVal.InvestValue - amount;
                }
                Update(retVal);
            }
        }

        public static void Add(BankDeposit bd)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "Select 1 from BANK_DEPOSIT where Client_Id = :v1 and Bank_Id = :v2 and Acc_no = :v3";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bd.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bd.BankId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bd.AccNo;
                cmd.Connection = con;
                object obj = cmd.ExecuteScalar();
                if (obj != null && int.Parse(obj.ToString()) == 1)
                {
                    Update(bd);
                    return;
                }
                cmd.CommandText = string.Empty;
                cmd.CommandText = "insert into BANK_DEPOSIT(CLIENT_ID, BANK_ID, ACC_NO, ACC_TYPE, INVEST_VALUE, INVEST_DATE," +
                                  "MATURITY_DATE, RATE, ACTIVE)";


                cmd.CommandText += "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9)";


                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = bd.AccType;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = bd.InvestValue;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = bd.InvestDate;
                cmd.Parameters.Add(":v7", OracleType.DateTime).Value = bd.MaturityDate;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = bd.Rate;

                if (bd.Active)
                {
                    cmd.Parameters.Add(":v9", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v9", OracleType.Number).Value = 0;
                }
                cmd.ExecuteNonQuery();
                con.Close();

                if (bd.Active)
                {                                      
                    AccuredInterestServices.CalculateAccruedInterest(bd.AccNo, DateTime.Today);
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(BankDeposit bd)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update BANK_DEPOSIT set " +
                                  "ACC_TYPE = :v4, " +
                                  "INVEST_VALUE = :v5, " +
                                  "INVEST_DATE = :v6, " +
                                  "MATURITY_DATE = :v7, " +
                                  "RATE = :v8, " +
                                  "ACTIVE = :v9 ";
                cmd.CommandText += " where CLIENT_ID = :v1 " +
                                      "and BANK_ID = :v2 " +
                                      "and ACC_NO = :v3";

                
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bd.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bd.BankId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bd.AccNo;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = bd.AccType;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = bd.InvestValue;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = bd.InvestDate;
                cmd.Parameters.Add(":v7", OracleType.DateTime).Value = bd.MaturityDate;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = bd.Rate;

                if (bd.Active)
                {
                    cmd.Parameters.Add(":v9", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v9", OracleType.Number).Value = 0;
                }
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                con.Close();

                if (bd.Active)
                {
                    AccuredInterestServices.CalculateAccruedInterest(bd.AccNo, DateTime.Today);
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
   
}
